﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;

namespace AlphaSolutionsGP
{
    public partial class frmStaffProfile : Form
    {
        private bool toModify;
        private Staff aStaff;

        public frmStaffProfile()
        {
            toModify = false;
            aStaff = new Staff();
            InitializeComponent();

            //set the date employed to value to a default value
            dtpStaffEmployedUntil.Format = DateTimePickerFormat.Custom;
            dtpStaffEmployedUntil.CustomFormat = " "; //set the date picker display to blank value

            //set the data employed from to a blank value as initial state
            dtpStaffEmployedFrom.Format = DateTimePickerFormat.Custom;
            dtpStaffEmployedFrom.CustomFormat = " ";

            //set the date of birth to a blank value as initial state
            dtStaffDob.Format = DateTimePickerFormat.Custom;
            dtStaffDob.MaxDate = DateTime.Now;
            dtStaffDob.Value = DateTime.Now;
            dtStaffDob.CustomFormat = " ";

            //set the Initial status for the staff as Live
            comboStaffStatus.Text = "Live";

            //generate a new staff id and insert it into the text box form
            generateStaffId();

            txtStaffUniversityEmail.Text = txtStaffID.Text + "@woodlands.ac.uk";

                      
        }

        public frmStaffProfile(Staff s)
        {
            //a staff "s" is being modified
            toModify = true;
            //copy that staff to be modified
            aStaff = s;
            InitializeComponent();
            //display "s" values
            displayStaffData();
        }

        private void displayStaffData()
        {            
            comboStaffStatus.Text = aStaff.Status;
            txtStaffID.Text = aStaff.StaffID.ToString();
            comboStaffRole.Text = aStaff.Role;
            txtStaffPassword.Text = aStaff.Password;
            dtpStaffEmployedFrom.MinDate = convertFromMySQLDate(aStaff.DateEmployedFrom); // set the min value for the staff employed from
            dtpStaffEmployedFrom.Value = convertFromMySQLDate(aStaff.DateEmployedFrom);

            if (aStaff.DateEmployedTo.Equals("01/01/0001 00:00:00"))
            {
                //set the datapicker to 01/01/0001 by customazing its value                
                dtpStaffEmployedUntil.Format = DateTimePickerFormat.Custom;
                dtpStaffEmployedUntil.CustomFormat = " ";
            }
            else 
            {
                dtpStaffEmployedUntil.MinDate = convertFromMySQLDate(aStaff.DateEmployedTo); // set the min value for the staff employed to
                dtpStaffEmployedUntil.Value = convertFromMySQLDate(aStaff.DateEmployedTo);    
            }

            txtStaffDormancy.Text = aStaff.ReasonForDormancy;
            txtStaffFirstName.Text = aStaff.FirstName;
            txtStaffMiddleName.Text = aStaff.MiddleName;
            txtStaffSurname.Text = aStaff.Surname;
            dtStaffDob.MaxDate = DateTime.Now.Date;
            dtStaffDob.Value = convertFromMySQLDate(aStaff.DOB);
            txtStaffNationality.Text = aStaff.Nationality;
            txtStaffPhone.Text = aStaff.TelephoneNumber;
            txtStaffMobile.Text = aStaff.MobileNumber;
            txtStaffUniversityEmail.Text = aStaff.EmailAddress;
            txtStaffStreet.Text = aStaff.StreetAddress;
            txtStaffTown.Text = aStaff.HomeTown;
            txtStaffCity.Text = aStaff.City;
            txtStaffPostcode.Text = aStaff.PostCode;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private bool staffValidate()
        {         

            bool flag = true;
            string alertBlankFields = "";
            string alertMobileNumb = "";
            string alertLandlineNum = "";
            string alertPostcode = "";

            //validate the left blank field
            if (comboStaffRole.Text == ""  || txtStaffFirstName.Text.Trim() == ""
                || txtStaffSurname.Text.Trim() == "" || txtStaffNationality.Text.Trim() == ""
                || txtStaffStreet.Text.Trim() == "" || txtStaffTown.Text.Trim() == "" || txtStaffCity.Text.Trim() == ""
                || txtStaffPostcode.Text.Trim() == "" || dtpStaffEmployedFrom.Text == "" || dtStaffDob.Text == "")
            {
                alertBlankFields = "All mandatory fields must not be blank";
                flag = false;
            }

            // mobile number validation
            if (txtStaffMobile.Text != "")
            {                
                Match matchMobile = Regex.Match(txtStaffMobile.Text.Trim(), @"^[0-9]+$"); //check for integer numbers only within the string 
                if (!matchMobile.Success) //if no characters were found
                {
                    alertMobileNumb = "You have entered the wrong mobile number.";
                    flag = false;
                }
            }

            // landline number validation
            if (txtStaffPhone.Text != "")
            {                
                Match matchLandline = Regex.Match(txtStaffPhone.Text.Trim(), @"^[0-9]+$"); //check for integer numbers only within the string 
                if (!matchLandline.Success) //if no characters were found
                {
                    alertLandlineNum = "You have entered the wrong landline number number.";
                    flag = false;
                }
            }

            //postcode validation
            if (txtStaffPostcode.Text != "")
            {
                // mobile number validation
                Match matchPostcode = Regex.Match(txtStaffPostcode.Text.Trim(), @"^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9\s]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$"); //check for valid postcode format
                if (!matchPostcode.Success) //if no characters were found
                {
                    alertPostcode = "The post code is not a valid postcode.";
                    flag = false;
                }
            }

            if (!alertBlankFields.Equals("") || !alertLandlineNum.Equals("") || !alertMobileNumb.Equals("") || !alertPostcode.Equals(""))
            {
                MessageBox.Show("" + alertBlankFields + "\n" + alertLandlineNum + "\n" + alertMobileNumb + "\n" + alertPostcode + "", "Filling fields error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }

                        
            return flag;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (staffValidate())
            {
                if (toModify)
                {
                    aStaff.Status = comboStaffStatus.Text;
                    aStaff.StaffID = txtStaffID.Text;
                    aStaff.Role = comboStaffRole.Text;
                    aStaff.Password = txtStaffPassword.Text.Trim();
                    aStaff.DateEmployedFrom = dtpStaffEmployedFrom.Text;
                    aStaff.DateEmployedTo = dtpStaffEmployedUntil.Text;
                    aStaff.ReasonForDormancy = txtStaffDormancy.Text.Trim();
                    aStaff.FirstName = txtStaffFirstName.Text.Trim();
                    aStaff.MiddleName = txtStaffMiddleName.Text.Trim();
                    aStaff.Surname = txtStaffSurname.Text.Trim();
                    aStaff.DOB = dtStaffDob.Text;
                    aStaff.Nationality = txtStaffNationality.Text.Trim();
                    aStaff.TelephoneNumber = txtStaffPhone.Text.Trim();
                    aStaff.MobileNumber = txtStaffMobile.Text.Trim();
                    aStaff.AssignedEmail = txtStaffUniversityEmail.Text.Trim();
                    aStaff.StreetAddress = txtStaffStreet.Text.Trim();
                    aStaff.HomeTown = txtStaffTown.Text.Trim();
                    aStaff.City = txtStaffCity.Text.Trim();
                    aStaff.PostCode = txtStaffPostcode.Text.Trim();

                    //modify sql
                    aStaff.amendStaff();
                    MessageBox.Show("The account for " + aStaff.FirstName + " " + aStaff.Surname + " has been amended.", "Amendment Successful", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    this.Close();
                }
                else
                {
                    aStaff.Status = comboStaffStatus.Text;
                    aStaff.StaffID = txtStaffID.Text;
                    aStaff.Role = comboStaffRole.Text;
                    aStaff.Password = txtStaffPassword.Text.Trim();
                    aStaff.DateEmployedFrom = dtpStaffEmployedFrom.Text;
                    aStaff.DateEmployedTo = dtpStaffEmployedUntil.Text;
                    aStaff.ReasonForDormancy = txtStaffDormancy.Text.Trim();
                    aStaff.FirstName = txtStaffFirstName.Text.Trim();
                    aStaff.MiddleName = txtStaffMiddleName.Text.Trim();
                    aStaff.Surname = txtStaffSurname.Text.Trim();
                    aStaff.DOB = dtStaffDob.Text;
                    aStaff.Nationality = txtStaffNationality.Text.Trim();
                    aStaff.TelephoneNumber = txtStaffPhone.Text.Trim();
                    aStaff.MobileNumber = txtStaffMobile.Text.Trim();
                    aStaff.AssignedEmail = txtStaffUniversityEmail.Text.Trim();
                    aStaff.StreetAddress = txtStaffStreet.Text.Trim();
                    aStaff.HomeTown = txtStaffTown.Text.Trim();
                    aStaff.City = txtStaffCity.Text.Trim();
                    aStaff.PostCode = txtStaffPostcode.Text.Trim();

                    //set the NULL value to those fields left blank
                    if (txtStaffMiddleName.Text == "")
                        aStaff.MiddleName = "NULL";
                    if (txtStaffMobile.Text == "")
                        aStaff.MobileNumber = "NULL";
                    if (txtStaffPhone.Text == "")
                        aStaff.TelephoneNumber = "NULL";
                    if (txtStaffDormancy.Text == "")
                        aStaff.ReasonForDormancy = "NULL";

                    //run sql to create new student
                    try
                    {
                        aStaff.createStaff();
                        MessageBox.Show("Staff has been created", "Success", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        this.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("There are problems with creating that staff. Here is the error message: " + ex.ToString());
                    }



                }
            }
            else
            {
                MessageBox.Show("Validation failure", "Failure", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
        }

        private void dtpStaffEmployedUntil_ValueChanged(object sender, EventArgs e)
        {
            dtpStaffEmployedUntil.Format = DateTimePickerFormat.Custom;
            dtpStaffEmployedUntil.CustomFormat = "dd/MM/yyyy";
        }        

        private void dtpStaffEmployedFrom_ValueChanged(object sender, EventArgs e)
        {
            if (!toModify) //will only execute the statement only when adding new staff
            {
                dtpStaffEmployedUntil.MinDate = dtpStaffEmployedFrom.Value; //set the date minimum employment untill to the current from date
            }
            dtpStaffEmployedFrom.Format = DateTimePickerFormat.Custom;
            dtpStaffEmployedFrom.CustomFormat = "dd/MM/yyyy";
        }

        private void btnCancel_Click_1(object sender, EventArgs e)
        {
            this.Close();
        }

        private void dtStaffDob_ValueChanged(object sender, EventArgs e)
        {
            dtStaffDob.Format = DateTimePickerFormat.Custom;
            dtStaffDob.CustomFormat = "dd/MM/yyyy";
        }
        
        //this is the pop up message to give a tip on the role field when the user would need to add a new staff
        private void showHelpRole()
        {
            MessageBox.Show("Please insert one of the following format role:\n" +
            "CL = Course Leader\n" +
            "ML = Module Leader\n" +
            "PT = Personal Tutor\n", 
            "Role Help",
            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            showHelpRole();
        }

        //generate a new staff id and insert it into the text box as appropriate
        private void generateStaffId()
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT MAX(staff_ID) FROM STAFF"; //select the biggest staff id
                int newStaffId = int.Parse(cmd.ExecuteScalar().ToString())+1; //convert the staff id selected and add one 
                txtStaffID.Text = newStaffId.ToString(); //insert the staff id into the taxt field
                aStaff.StaffID = newStaffId.ToString(); //store the value into the attribute ready to be inserted into the STAFF table
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }

            finally
            {
                connection.Close();

            }
        }

        //DINAMICALLY FILL IN A DROPDOWN MENU
        private void fillInCombo( ComboBox cb, string column, string table)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {                
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT "+column+" FROM " + table + ""; //select the biggest staff id
                MySqlDataReader myReader = cmd.ExecuteReader();
                string nameColumn = ""+column+"";
                while (myReader.Read())
                {
                    column = myReader.GetString(nameColumn);
                    cb.Items.Add(column);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }

            finally
            {
                connection.Close();

            }
        }

        public static DateTime convertFromMySQLDate(string dt)
        {
            //string dateToString = Convert.ToString(date);
            DateTime firstFormatDate = DateTime.Parse(dt);
            string dateString = firstFormatDate.ToString("dd/MM/yyyy");
            DateTime finalFormatDate = DateTime.Parse(dateString);
            return finalFormatDate.Date;
        }

        private void comboStaffStatus_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboStaffRole.SelectedIndex.ToString().Equals("Live"))
            {
                dtpStaffEmployedUntil.Enabled = false;
            }
            else
            {
                dtpStaffEmployedUntil.Enabled = true;
            }
            
        }

       
    }       
}
